﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Collections;
using System.Data.OleDb;
using System.Data;

namespace BaseHelper.Data
{
    public class OleDBHelper
    {
        public string Error;
        private string str;


        public OleDBHelper(DBInfo db)
        {
            str = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + db.Path;
        }




        public bool Open(DBInfo db) 
        {
            str = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + db.Path;
            try
            {
                using (OleDbConnection conn = new OleDbConnection(str))
                {
                    conn.Open();
                    Error = "";
                    return true;
                }
            }
            catch (Exception ex){
                Error = ex.Message;
                return false;
            }
        }

        public Hashtable[] ExecuteHashtable(string sql) 
        {

            Hashtable[] hts=null;
            try
            {
                using (OleDbConnection conn = new OleDbConnection(str))
                {
                    using (OleDbCommand cmd = new OleDbCommand(sql, conn))
                    {
                        OleDbDataReader dr = cmd.ExecuteReader();
                        if (dr.HasRows)
                        {
                            hts = DataReaderToHashtable(dr);
                        }
                        if (!dr.IsClosed)
                            dr.Close();
                        Error = "";
                    }
                }
            }
            catch (Exception ex)
            {
                Error = ex.Message;
            }
            return hts;
        }


        public bool IsDataReaderHasRow(string sql)
        {
            bool hasRow = false;
            try
            {
                using (OleDbConnection conn = new OleDbConnection(str))
                {
                    using (OleDbCommand cmd = new OleDbCommand(sql, conn))
                    {

                        OleDbDataReader dr = cmd.ExecuteReader();
                        hasRow = dr.HasRows;
                        if (!dr.IsClosed)
                            dr.Close();
                        Error = "";
                    }
                }
            }
            catch (Exception ex)
            {
                Error = ex.Message;
                LogHelper.Err("RT.Helper.Data.OleDBHelper.cs", "IsDataReaderHasRow()", ex);
            }
            return hasRow;
        }

        public int NonQuery(string sql)
        {
            try
            {
                using (OleDbConnection conn = new OleDbConnection(str))
                {
                    using (OleDbCommand cmd = new OleDbCommand(sql, conn))
                    {
                        Error = "";
                        return cmd.ExecuteNonQuery() > 0 ? 1 : 0;
                    }
                }
            }
            catch(Exception ex) 
            {
                Error = ex.Message;
                LogHelper.Err("RT.Helper.Data.OleDBHelper.cs", "NonQuery()", ex);
            }
            return -1;
        }

        public static Hashtable[] DataReaderToHashtable(OleDbDataReader dr)
        {
            List<Hashtable> lt = new List<Hashtable>();
         
            while (dr.Read())
            {
                Hashtable ht = new Hashtable();
                for (int i = 0; i < dr.FieldCount; i++)
                    ht.Add(dr.GetName(i), System.Web.HttpUtility.HtmlDecode(dr[i].ToString()));
                lt.Add(ht);
            }

            return lt.ToArray();
        }

        public int Insert(string table,Hashtable ht)
        {
            string keys = "";
            string values = "";
            foreach (string key in ht.Keys)
            {
                keys += "[" + key + "],";
                values += "'" + System.Web.HttpUtility.HtmlEncode(ht[key].ToString()) + "',";
            }
            if (keys.Length > 0)
            {
                keys = keys.Substring(0, keys.Length - 1);
                values = values.Substring(0, values.Length - 1);
            }

            string sql = string.Format("insert into {0}({1}) values({2})",table,keys,values);
            return NonQuery(sql);
        }

        public Hashtable[] Select(string table, string where = "", string field = "*", string orderby = "",int page=-1,int pageSize=30)
        {
            string sql;
            if (page == -1)
                sql = string.Format("select {0} from {1} {2}  {3}", field, table, where.Length > 0 ? "where " + where : "", orderby.Length > 0 ? "order by " + orderby : "");
            else
            {
                sql = string.Format("select {0} from {1} {2}  {3}", field, table, where.Length > 0 ? "where " + where : "", orderby.Length > 0 ? "order by " + orderby : "");
                
                //3.12 屏蔽一下，部署到vps上，暂时不需要
                //page--;
                //sql = string.Format("select * from (select top {0} * from (select top {1} {2} from {3} {4}) {5}) {5}", pageSize, page * pageSize, field, table, where.Length > 0 ? "where " + where : "", orderby.Length > 0 ? "order by " + orderby : "");
            }
            return ExecuteHashtable(sql);
        }

        public bool IsSelectHasRow(string table, string where = "")
        {
            string sql = string.Format("select * from {0} {1}", table, where.Length>0?"where " + where:"");
            return IsDataReaderHasRow(sql);
        }

       
        public int Update(string table,Hashtable ht,string where)
        {
            string values = "";
            foreach (string key in ht.Keys)
            {
                values += "[" + key + "]='" + System.Web.HttpUtility.HtmlEncode(ht[key].ToString()) + "',";
            }
            if (values.Length > 0)
            {
                values = values.Substring(0, values.Length - 1);
            }
            
            string sql = string.Format("UPDATE {0} set {1} where {2}",  table,values,where);
            return NonQuery(sql);
        }
        
        public int Delete(string table, string where="")
        {
            string sql = string.Format("delete from {0} {1}", table, where.Length > 0 ? "where " + where : "");
            return NonQuery(sql);
        }

        public int Truncate(string tableName)
        {
            string sql = "delete from " + tableName;
            return NonQuery(sql);
        }

    }
}

